package com.ridbor001.demo.dao;

import com.ridbor001.demo.bean.RDBGrade;
import com.ridbor001.demo.util.RDBDataStore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

public class RDBGradeDAO {
    RDBDataStore dataStore = new RDBDataStore();
    // 批量获取成绩
    public  Map<String, RDBGrade> getGradesByIds(Set<String> gradeIds) throws SQLException {
        if (gradeIds == null || gradeIds.isEmpty()) {
            return Collections.emptyMap();
        }

        String placeholders = gradeIds.stream()
                .map(id -> "?")
                .collect(Collectors.joining(","));

        String sql = "SELECT * FROM grades WHERE grade_id IN (" + placeholders + ")";
        Map<String, RDBGrade> gradeMap = new HashMap<>();

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int index = 1;
            for (String gradeId : gradeIds) {
                pstmt.setString(index++, gradeId);
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    RDBGrade grade = new RDBGrade(
                            rs.getString("grade_id"),
                            rs.getString("student_id"),
                            rs.getString("course_id"),
                            rs.getString("term"),
                            rs.getDouble("daily_score"),
                            rs.getDouble("stage_score"),
                            rs.getDouble("final_score")
                    );
                    gradeMap.put(grade.getGradeId(), grade);
                }
            }
        }
        return gradeMap;
    }
    // 批量获取课程学生成绩
    public  Map<String, RDBGrade> getGradesByCourseAndStudents(
            String courseId, List<String> studentIds) throws SQLException {

        if (studentIds == null || studentIds.isEmpty()) {
            return Collections.emptyMap();
        }

        Map<String, RDBGrade> gradeMap = new HashMap<>();

        // 使用IN查询一次性获取所有成绩
        String placeholders = studentIds.stream()
                .map(id -> "?")
                .collect(Collectors.joining(","));

        String sql = "SELECT * FROM grades " +
                "WHERE course_id = ? AND student_id IN (" + placeholders + ")";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, courseId);

            for (int i = 0; i < studentIds.size(); i++) {
                pstmt.setString(i + 2, studentIds.get(i));
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    RDBGrade grade = new RDBGrade(
                            rs.getString("grade_id"),
                            rs.getString("student_id"),
                            rs.getString("course_id"),
                            rs.getString("term"),
                            rs.getDouble("daily_score"),
                            rs.getDouble("stage_score"),
                            rs.getDouble("final_score")
                    );
                    gradeMap.put(grade.getStudentId(), grade);
                }
            }
        }
        return gradeMap;
    }
    // 根据学生和课程获取成绩
    public  Optional<RDBGrade> findGradeByStudentAndCourse(String studentId, String courseId) throws SQLException {
        String sql = "SELECT * FROM grades "
                + "WHERE student_id = ? AND course_id = ?";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, studentId);
            pstmt.setString(2, courseId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(new RDBGrade(
                            rs.getString("grade_id"),
                            rs.getString("student_id"),
                            rs.getString("course_id"),
                            rs.getString("term"),
                            rs.getDouble("daily_score"),
                            rs.getDouble("stage_score"),
                            rs.getDouble("final_score")
                    ));
                }
            }
        }
        return Optional.empty();
    }
    // 添加成绩
    public   boolean addGrade(RDBGrade grade) throws SQLException {
        String sql = "INSERT INTO grades (grade_id, student_id, course_id, term, "
                + "daily_score, stage_score, final_score) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, grade.getGradeId());
            pstmt.setString(2, grade.getStudentId());
            pstmt.setString(3, grade.getCourseId());
            pstmt.setString(4, grade.getTerm());
            pstmt.setDouble(5, grade.getDailyScore());
            pstmt.setDouble(6, grade.getStageScore());
            pstmt.setDouble(7, grade.getFinalScore());

            return pstmt.executeUpdate() > 0;
        }
    }
    public boolean upsertGrade(RDBGrade grade) throws SQLException {
        String query = "SELECT COUNT(*) FROM grades WHERE grade_id = ?";
        String insert = "INSERT INTO grades (grade_id, student_id, course_id, term, daily_score, stage_score, final_score) VALUES (?, ?, ?, ?, ?, ?, ?)";
        String update = "UPDATE grades SET student_id = ?, course_id = ?, term = ?, daily_score = ?, stage_score = ?, final_score = ? WHERE grade_id = ?";

        try (Connection conn = dataStore.getConnection()) {
            try (PreparedStatement checkStmt = conn.prepareStatement(query)) {
                checkStmt.setString(1, grade.getGradeId());
                ResultSet rs = checkStmt.executeQuery();
                rs.next();
                boolean exists = rs.getInt(1) > 0;

                if (exists) {
                    try (PreparedStatement updateStmt = conn.prepareStatement(update)) {
                        updateStmt.setString(1, grade.getStudentId());
                        updateStmt.setString(2, grade.getCourseId());
                        updateStmt.setString(3, grade.getTerm());
                        updateStmt.setDouble(4, grade.getDailyScore());
                        updateStmt.setDouble(5, grade.getStageScore());
                        updateStmt.setDouble(6, grade.getFinalScore());
                        updateStmt.setString(7, grade.getGradeId());
                        return updateStmt.executeUpdate() > 0;
                    }
                } else {
                    try (PreparedStatement insertStmt = conn.prepareStatement(insert)) {
                        insertStmt.setString(1, grade.getGradeId());
                        insertStmt.setString(2, grade.getStudentId());
                        insertStmt.setString(3, grade.getCourseId());
                        insertStmt.setString(4, grade.getTerm());
                        insertStmt.setDouble(5, grade.getDailyScore());
                        insertStmt.setDouble(6, grade.getStageScore());
                        insertStmt.setDouble(7, grade.getFinalScore());
                        return insertStmt.executeUpdate() > 0;
                    }
                }
            }
        }
    }
    public   boolean gradeExists(String gradeId) throws SQLException {
        final String sql = "SELECT COUNT(*) FROM grades WHERE grade_id = ?";
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, gradeId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }
    public boolean deleteGrade(String gradeId) throws SQLException {
        final String sql = "DELETE FROM grades WHERE grade_id = ?";
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, gradeId);
            return pstmt.executeUpdate() > 0;
        }
    }
    // 根据成绩ID获取成绩
    public   Optional<RDBGrade> findGradeById(String gradeId) throws SQLException {
        String sql = "SELECT * FROM grades WHERE grade_id = ?";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, gradeId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(new RDBGrade(
                            rs.getString("grade_id"),
                            rs.getString("student_id"),
                            rs.getString("course_id"),
                            rs.getString("term"),
                            rs.getDouble("daily_score"),
                            rs.getDouble("stage_score"),
                            rs.getDouble("final_score")
                    ));
                }
            }
        }
        return Optional.empty();
    }
    public   boolean updateGrade(RDBGrade grade) throws SQLException {
        String sql = "UPDATE grades SET daily_score = ?, stage_score = ?, final_score = ? WHERE grade_id = ?";
        try (Connection conn = dataStore.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setDouble(1, grade.getDailyScore());
            stmt.setDouble(2, grade.getStageScore());
            stmt.setDouble(3, grade.getFinalScore());
            stmt.setString(4, grade.getGradeId());

            int affectedRows = stmt.executeUpdate();
            return affectedRows > 0;
        }
    }

}
